package com.canin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Map;
import java.util.TreeMap;

import com.canin.model.Collar;
import com.canin.util.CaninUtil;

public class CollarDAO
{
    private Connection conn = null;

    private Map<String, String> sizes = CaninUtil.initializeSizes();
	private Map<String, String> collarTypes = CaninUtil.initializeCollarType();

    public CollarDAO(Connection conn)
    {
        this.conn = conn;
    }

    public ArrayList<Collar> getAllCollar() throws Exception
    {
        ArrayList<Collar> collarList = new ArrayList<Collar>();

        String sql = "select * from collar";
        PreparedStatement ps = conn.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        while (rs.next())
        {
            Collar collar = new Collar();
            collar.setId( rs.getInt( "id" ) );
            collar.setName( rs.getString( "name" ) );
            collar.setBrand( rs.getString( "brand" ) );
            collar.setDescription( rs.getString( "description" ) );
            collar.setSize( rs.getString( "size" ) );
            collar.setWidht( rs.getDouble( "width" ) );
            collar.setMinLength( rs.getDouble( "minLength" ) );
            collar.setMaxLength( rs.getDouble( "maxLength" ) );
            collar.setCollarType( rs.getString( "collarType" ) );
            collar.setRawMaterial( rs.getString( "rawMaterial" ) );
            collar.setShippingPrice( rs.getInt( "shippingPrice" ) );
            collar.setPrice( rs.getInt( "price" ) );
            
            if ( collar.getSize() != null
            	&& !"".equals( collar.getSize() ) )
            {
            	collar.setThaiSize( sizes.get( collar.getSize() ) );
            }
            
            collar.setThaiCollarType( collarTypes.get( collar.getCollarType() ) );
            
            collarList.add( collar );
        }

        rs.close();
        ps.close();

        return collarList;
    }
	
	public TreeMap<String, String> getCollarBrand() throws Exception
	{
		TreeMap<String, String> collarBrandList = new TreeMap<String, String>();
		
		String sql = "select distinct(brand) from collar where brand!=''";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			collarBrandList.put( rs.getString( "brand" ), rs.getString( "brand" ) );
		}

		rs.close();
		ps.close();
		
		return collarBrandList;
	}
	
	public void addNewCollar( Collar collar ) throws Exception
	{
		String sql = "insert into collar(name, brand, description, size, price, shippingPrice," +
				" width, minLength, maxLength, collarType, rawMaterial)" +
				" value(?,?,?,?,?,?,?,?,?,?,?)";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, collar.getName());
		ps.setString(2, collar.getBrand());
		ps.setString(3, collar.getDescription());
		ps.setString(4, collar.getSize());
		ps.setInt(5, collar.getPrice());
		ps.setInt(6, collar.getShippingPrice());
		ps.setDouble(7, collar.getWidht());
		ps.setDouble(8, collar.getMinLength());
		ps.setDouble(9, collar.getMaxLength());
		ps.setString(10, collar.getCollarType());
		ps.setString(11, collar.getRawMaterial());
		
		ps.executeUpdate();
		ps.close();
	}

    public ArrayList<Collar> getCollarBySize( String size ) throws Exception
    {
        ArrayList<Collar> collarList = new ArrayList<Collar>();

        String sql = "select * from collar where size=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, size);

        ResultSet rs = ps.executeQuery();

        while (rs.next())
        {
            Collar collar = new Collar();
            collar.setId( rs.getInt( "id" ) );
            collar.setName( rs.getString( "name" ) );
            collar.setBrand( rs.getString( "brand" ) );
            collar.setDescription( rs.getString( "description" ) );
            collar.setSize( rs.getString( "size" ) );
            collar.setWidht( rs.getDouble( "width" ) );
            collar.setMinLength( rs.getDouble( "minLength" ) );
            collar.setMaxLength( rs.getDouble( "maxLength" ) );
            collar.setCollarType( rs.getString( "collarType" ) );
            collar.setRawMaterial( rs.getString( "rawMaterial" ) );
            collar.setShippingPrice( rs.getInt( "shippingPrice" ) );
            collar.setPrice( rs.getInt( "price" ) );
            collar.setImageName( rs.getString( "imageName" ) );
            
            if ( collar.getSize() != null
            	&& !"".equals( collar.getSize() ) )
            {
            	collar.setThaiSize( sizes.get( collar.getSize() ) );
            }
            
            collar.setThaiCollarType( collarTypes.get( collar.getCollarType() ) );
            
            collarList.add( collar );
        }

        rs.close();
        ps.close();

        return collarList;
    }
    
    
}
